package com.kingschan.jeasytag.sql.sqlparse;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Limit;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.Top;
import net.sf.jsqlparser.statement.select.Union;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import com.kingschan.jeasytag.enums.DbType;
import com.kingschan.jeasytag.exception.SQLCommandException;
import com.kingschan.util.lang.StringUtil;
/**
 * 通用SQL查询语句解析  只能解析查询语名
 * @author kingschan
 *date:2013-7-18
 *<font style="color:red;">having 和 top 未实现</font>
 */
public class GenericSelectParse implements AbstructSelectParse{
	private String sql;
	private PlainSelect ps;
	private Union union;
	private DbType dbtype;
	private CCJSqlParserManager pm = new CCJSqlParserManager();
	//sql templeate
	private static final String ORACLE_pagination="select * from (select jop.*,rownum rn from (${sql}) jop where rownum <= ${endrow}) where rn >= ${beginrow}";
	
	public GenericSelectParse(String p_sql,DbType P_dbtype)throws Exception{
		this.sql=p_sql;
		this.dbtype=P_dbtype;
		Statement statement= pm.parse(new StringReader(sql));
		if (statement instanceof Select) {
			Select select = (Select) statement;
			if (select.getSelectBody() instanceof PlainSelect) {
				ps = (PlainSelect) select.getSelectBody();
							
			} else {
				union = (Union) select.getSelectBody();				
			}
		} else {
			throw new SQLCommandException(
					String
							.format(
									"this sql command is not a select command please change it ! %s",
									this.sql));
		}
	}
	
	/**
	 * 返回selectbody
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public SelectBody getSelectBody(String sql)throws SQLCommandException {
		Statement statement = null;
		try {
			statement = pm.parse(new StringReader(sql));
		} catch (JSQLParserException e) {
			throw new SQLCommandException(String.format("SQL语法错误:%s", sql));
		}
		Select select = (Select) statement;
		return null==select?null:select.getSelectBody();
		
	}
	
	@Override
	public void setSelectItem(List<String> lis) throws SQLCommandException {
		if (null!=ps) {
			//普通的语句
			ps.setSelectItems(lis);
			return ;
		}
		//union 语句
		@SuppressWarnings("unchecked")
		List<PlainSelect> plis = union.getPlainSelects();
		for (PlainSelect plainSelect : plis) {
			plainSelect.setSelectItems(lis);
		}
	}

	@Override
	public void appendWhere(String where_str) throws Exception {
		if (StringUtil.replaceSpaceLine(where_str).length()==0)return;		
		if (where_str.toLowerCase().contains("where"))throw new SQLCommandException(" condition string can't contain [where] keyword !!!");
		
		if (null!=ps) {
			//普通的语句
			this.ps=setWhere(this.ps,where_str);
			return ;
		}
		//union 语句
		@SuppressWarnings("unchecked")
		List<PlainSelect> plis = union.getPlainSelects();
		List<PlainSelect> templis=new ArrayList<PlainSelect>();
		for (PlainSelect plainSelect : plis) {
			templis.add(setWhere(plainSelect,where_str));
		}
		union.setPlainSelects(templis);
		
	}

	
	private PlainSelect setWhere(PlainSelect pselect,String str)throws Exception{
		PlainSelect tempselect;
		String tempSQL;
		if (null==pselect.getWhere()) {
			if(str.trim().matches("^[and]{3}|[or]{2}"))throw new SQLCommandException("当前语句没有where条件，第一个条件不能以and,or 开头");
			//不存在where条件
			tempselect=pselect;
			pselect.setGroupByColumnReferences(null);
			pselect.setOrderByElements(null);
			pselect.setLimit(null);
			pselect.setHaving(null);
			tempSQL=pselect.toString().concat(" where ").concat(str);
			pselect = (PlainSelect) this.getSelectBody(tempSQL);
			pselect.setGroupByColumnReferences(tempselect.getGroupByColumnReferences());
			pselect.setOrderByElements(tempselect.getOrderByElements());
			pselect.setLimit(tempselect.getLimit());
			pselect.setHaving(tempselect.getHaving());
		}else{
			//存在where条件
			if(str.trim().matches("^[and]{3}|[or]{2}"))throw new SQLCommandException("当前语句有where条件，第一个条件必须以and,or 开头");
			String where=ps.getWhere()+" "+str;
			tempSQL=ps.toString().replace(ps.getWhere().toString(), where);
			pselect = (PlainSelect) this.getSelectBody(tempSQL);			
		}
		return pselect;
	}
	
	@Override
	public void paginationLimit(int pageindex, int pagesize)throws Exception {
		switch (this.dbtype) {
		case MYSQL:
			Limit limit = new Limit();
			limit.setOffset((pageindex - 1) * pagesize);
			limit.setRowCount(pagesize);
			if (null!=ps) {
				ps.setLimit(limit);
			} else if(null!=union) {
				union.setLimit(limit);
			}
			break;
		//case SQLSERVER:
				//throw new SQLCommandException(" can't support sqlserver pagination");
		//	break;
		case ORACLE:
			Integer beginrow=(pageindex-1)*pagesize;
			Integer endrow=(pageindex-1)*pagesize+pagesize;
			String paginationSQl=ORACLE_pagination.replace("${sql}", this.toString())
			.replace("${beginrow}", beginrow.toString())
			.replace("${endrow}", endrow.toString());			
			this.ps= (PlainSelect) this.getSelectBody(paginationSQl);
			break;
		default:
				throw new SQLCommandException(" can't support sqlserver pagination");
		}
		
	}

	@Override
	public void setOrderby(List<String> lis) throws Exception {
		if (null!=ps) {
			ps.setOrderByElements(lis);
			return;
		}
		union.setOrderByElements(lis);
		
	}

	@Override
	public void setGroupby(List<String> groupby) throws Exception {
		if (null!=ps) {
			ps.setGroupByColumnReferences(groupby);
			return;
		}
		@SuppressWarnings("unchecked")
		List<PlainSelect> lis = union.getPlainSelects();
		for (PlainSelect plainSelect : lis) {
			plainSelect.setGroupByColumnReferences(groupby);
		}
		union.setPlainSelects(lis);
		
	}

	@Override
	public void setHaving(String having) throws Exception {
		
		
	}

	@Override
	public void setTop(long rowCount) throws Exception {		
		if (this.dbtype!=DbType.SQLSERVER) {
			throw new SQLCommandException("只能SQLSERVER才能设置Top");
		}
		Top top = new Top();
		top.setRowCount(rowCount);
		if (null!=ps) {
			ps.setTop(top);
		}
		
	}

	@Override
	public void setLimit(long offset, long rowCount) throws Exception {
		if (this.dbtype!=DbType.MYSQL) {
			throw new SQLCommandException("只能MYSQl才能设置limit");
		}
		Limit limit = new Limit();
		limit.setOffset(offset);
		limit.setRowCount(rowCount);
		if (null!=ps) {
			ps.setLimit(limit);
		}else if (null!=union) {
			union.setLimit(limit);
		}
	}
	@Override
	public String toString() {
		String sql ="";
		if (null!=ps) {
			sql= ps.toString();
		} else {
			sql =union.toString();
		}
		//ORACLE grammer can't table Aliases  this is jsqlparse bug
		if (this.dbtype==DbType.ORACLE) {
			sql=sql.replaceFirst("AS{1}+\\s[jop]{3}", "jop"); 
		}
		return sql;
	}
	
	
	
	
	public PlainSelect getPs() {
		return ps;
	}

	public void setPs(PlainSelect ps) {
		this.ps = ps;
	}

	public Union getUnion() {
		return union;
	}

	public void setUnion(Union union) {
		this.union = union;
	}

	public static void main(String[] args) throws Exception {
		//String SQL="  select id,parentid,cityname,regiontype,agencyid from espcms_city union all select id,parentid,cityname,regiontype,agencyid from espcms_city ";
		String SQL="select a,b,c,d from aaa where a=a and b=1 or c=1 and d in (1,2,2,33,4)";
		GenericSelectParse gs = new GenericSelectParse(SQL, DbType.ORACLE);
		Expression where =gs.getPs().getWhere();
		
		ExpressionDeParser expressionDeParser = new ExpressionDeParser();
		//Myparse m = new Myparse();
		StringBuffer stringBuffer = new StringBuffer();
		expressionDeParser.setBuffer(stringBuffer);
		gs.getPs().getWhere().accept(expressionDeParser);
		//System.out.println(stringBuffer.toString());
		
		
	}

}
